package com.api.gc;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.api.gc.utils.Result;
import com.sun.jersey.core.header.FormDataContentDisposition;
import com.sun.jersey.multipart.FormDataParam;
import com.weaver.esb.client.EsbClient;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.*;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

import com.weaver.esb.spi.EsbService;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;

/**
 * @author Tyc
 * @create 2024/03/27
 * @description
 */
@Path("/uploadTemplate")
public class UploadFIle {

    private static final String  OUTPUT_TIME_FORMAT = "yyyy-MM-dd";

    private static final String INPUT_TIME_FORMAT_2 = "yyyy/MM/dd";

    private static DataFormatter dataFormatter = new DataFormatter();
    public static String validateAndFormat(String inputTime) {
        try {
            // 创建用于解析输入时间的 SimpleDateFormat 对象
            SimpleDateFormat inputParser = new SimpleDateFormat(INPUT_TIME_FORMAT_2);

            // 尝试解析输入字符串
            Date parsedDate = inputParser.parse(inputTime);

            // 创建用于格式化目标时间的 SimpleDateFormat 对象
            SimpleDateFormat outputFormatter = new SimpleDateFormat(OUTPUT_TIME_FORMAT);

            // 格式化解析得到的日期为所需格式
            String formattedTime = outputFormatter.format(parsedDate);

            return formattedTime;
        } catch (ParseException e) {
            // 解析过程中发生异常，说明输入字符串不是有效的时间格式
            System.err.println("Invalid time format: " + inputTime);
            return inputTime;
        }
    }

    private static String getExtension(String fileName) {
        return fileName.substring(fileName.lastIndexOf(".") + 1);
    }

    /**
     * 上传excel到指定路径  /data/unstructured/template/tempFile
     * @return
     */
    @Path("/uploadExcel")
    @POST
    @Consumes(MediaType.MULTIPART_FORM_DATA)
    @Produces(MediaType.APPLICATION_JSON)
    public Result  uploadExcel( @FormDataParam("file") InputStream inputStream
            ,@FormDataParam("file") FormDataContentDisposition cp) throws IOException {
        try{
            // 获取文件名和内容
            String originalFileName = cp.getFileName();
            // 生成UUID作为文件名
            String uuidFileName = UUID.randomUUID() + "." + getExtension(originalFileName);
            // 保存文件到指定路径
            Files.copy(inputStream, Paths.get("/data/unstructured/template/tempFile/"+ uuidFileName), StandardCopyOption.REPLACE_EXISTING);
            return Result.ok(uuidFileName);
        }catch (Exception EX){
            new BaseBean().errorLog("上传文件失败",EX);
            return Result.ok(EX.getMessage(),null);
        }
    }

    /**
     * 解析excel
     * @param fileName  生成的临时文件名称
     * @param configJson
     * @param request
     * @param response
     * @return
     * @throws IOException
     */
    @Path("/analyzeData")
    @POST
    @Produces(MediaType.APPLICATION_JSON)
    public Result analyzeData(@QueryParam("fileName") String fileName,@QueryParam("configJson") String configJson,@Context HttpServletRequest request, @Context HttpServletResponse response) throws IOException {
        Map<String,String> SQL_MAP = new HashMap<>();
        Map<String,String> SQL_RES = new HashMap<>();
        Map<String,String> ESB_MAP = new HashMap<>();
        File saveFile = new File("/data/unstructured/template/tempFile/"+fileName);
        try{
            System.out.println("fileName:"+fileName);
            System.out.println("configJson:"+configJson);
            JSONArray configJsonArray = JSONArray.parseArray(configJson);//获取字段特殊配置
            //如果存在配置  则对配置做整理
            Map<String,Config> configMap = new HashMap<>();
            if (configJsonArray.size() > 0){
                for (int i=0;i<configJsonArray.size();i++){
                    JSONObject item = configJsonArray.getJSONObject(i);
                    String column = item.getString("column");//获取字段名称
                    if (column != null && column.length() > 0){
                        Config config = new Config();
                        config.setType(item.getString("type"));//获取字段类型  text 文本  check  选择框  browser  浏览框
                        config.setKvMode(item.getString("kvMode"));  //"first" "index" "last"   如果为浏览按钮的话取第一个值还是最后一个值还是指定顺序的值  如果选择了index则需要制定顺序  默认first
                        config.setIndex(item.getInteger("index"));//如果kvMode为index时，指定顺序   指定顺序大于数组长度-1  则取最后一个
                        config.setMulti(item.getBoolean("multi"));//是否多选  默认否 即单选
                        config.setSqlid(item.getString("sqlid"));//获取sqlid
                        config.setEventkey(item.getString("eventkey"));//获取事件标识
                        config.setDict(item.getJSONObject("dict"));//获取字典项
                        config.setEventjson(item.getString("eventjson"));//设置消息体
                        config.setEventResKey(item.getString("eventResKey"));//设置回显字段key
                        configMap.put(column,config);
                    }
                }
            }
            System.out.println("解析完成config:"+configMap);
            System.out.println(">>>>>>>>开始读取上传文件>>>>>>");
            System.out.println("文件路径"+"/data/unstructured/template/tempFile/"+fileName);
            FileInputStream fileInputStream = new FileInputStream(saveFile);
            String fileExtension = fileName.substring(fileName.lastIndexOf(".") + 1);
            try  {
                //读取文件内容
                Workbook workbook;
                if ("xls".equals(fileExtension)) {
                    workbook = new HSSFWorkbook(fileInputStream);
                } else if ("xlsx".equals(fileExtension)) {
                    workbook = new XSSFWorkbook(fileInputStream);
                } else {
                    return Result.error("上传的文件格式不正确");
                }
                Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
                Row titleRow = sheet.getRow(0);//获取第一行表头  根据表头获取字段名
                List<String> fieldList = new LinkedList<>();
                for (Cell cell : titleRow){
                    String cellValue = getCellValue(cell).trim();
                    String fieldKey = cellValue.split("-")[1];
                    fieldList.add(fieldKey);
                }
                JSONArray jsonArray = new JSONArray();
                for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    JSONObject item = new JSONObject();
                    Row row = sheet.getRow(rowIndex);
                    for (int i=0;i< fieldList.size();i++) {
                        String cellValue = getCellValue(row.getCell(i)).trim();
                        String column =fieldList.get(i);//获取到列字段名
                        System.out.println("打印上传的模板文件行内容"+column+":"+cellValue);
                        if (Objects.isNull(cellValue)&&"".equals(cellValue)){
                            continue;
                        }
                        if (configMap.containsKey(column)){//判断该字段是否在特殊配置中  如果在 则分情况进行特殊处理
                            Config config = configMap.get(column);
                            System.out.println("开始解析特殊配置字段！"+column);
                            String type = config.getType();
                            if(type.equals("check")){
                                JSONObject dict = config.getDict();
                                if (dict.keySet().size()>0){//说明 存在自定义字典项
                                    item.put(column,dict.get(cellValue));
                                    System.out.println("字典项赋值完成由【"+cellValue+"】转为【"+dict.get(cellValue)+"】");
                                }else if (!Objects.isNull(config.getSqlid())){//如果sqlid不为空就按照sqlid进行查询
                                    String real_sql = SQL_MAP.get(config.getSqlid());
                                    if (Objects.isNull(real_sql)){
                                        RecordSet rs = new RecordSet();
                                        System.out.println("开始执行SQL");
                                        rs.executeQuery("select real_sql from OA_CUSTOM_SQL_CONFIG where sql_id=?",config.getSqlid());
                                        if(rs.next()){
                                             real_sql = rs.getString(1);
                                            SQL_MAP.put(config.getSqlid(),real_sql);
                                            String rep_sql = real_sql.replaceAll("%s",cellValue);
                                            if (!Objects.isNull(SQL_RES.get(rep_sql))){
                                                item.put(column, SQL_RES.get(rep_sql));
                                                continue;
                                            }
                                            RecordSet rs1 = new RecordSet();
                                            rs1.executeQuery(rep_sql);
                                            if (rs1.next()){
                                                Object data = rs1.getArray().get(0);
                                                if (data.getClass().isArray()) {
                                                    Object[] dataArray = (Object[]) data;
                                                    if (dataArray.length > 0) {
                                                        Object element = dataArray[0];
                                                        if (element != null) {
                                                            String strValue = String.valueOf(element);
                                                             item.put(column, strValue);
                                                        } else {
                                                            item.put(column, "");
                                                        }
                                                    } else {
                                                        item.put(column, "");
                                                    }
                                                } else {
                                                    item.put(column, data);
                                                }
                                                SQL_RES.put(rep_sql,String.valueOf(item.get(column)));
                                            }
                                        }
                                    }else{
                                        String rep_sql = real_sql.replaceAll("%s",cellValue);
                                        if (!Objects.isNull(SQL_RES.get(rep_sql))){
                                            item.put(column, SQL_RES.get(rep_sql));
                                            continue;
                                        }
                                        RecordSet rs1 = new RecordSet();
                                        rs1.executeQuery(rep_sql);
                                        if (rs1.next()){
                                            Object data = rs1.getArray().get(0);
                                            if (data.getClass().isArray()) {
                                                Object[] dataArray = (Object[]) data;
                                                if (dataArray.length > 0) {
                                                    Object element = dataArray[0];
                                                    if (element != null) {
                                                        String strValue = String.valueOf(element);
                                                        item.put(column, strValue);
                                                    } else {
                                                        item.put(column, "");
                                                    }
                                                } else {
                                                    item.put(column, "");
                                                }
                                            } else {
                                                item.put(column, data);
                                            }
                                            SQL_RES.put(rep_sql,String.valueOf(item.get(column)));
                                        }
                                    }
                                }
                            }else if (type.equals("browser")){
                                cellValue = cellValue.trim();
                                if (!Objects.isNull(config.getEventkey())){//浏览框字段  有查询事件标识
                                    String val = ESB_MAP.get(config.getEventkey()+"_"+config.getEventjson().replaceAll("%s",cellValue));
                                    if (Objects.isNull(val)){
                                        //触发 ESB  事件
                                        EsbService service = EsbClient.getService();
                                        System.out.println("eventKey:"+config.getEventkey());
                                        System.out.println("eventJson:"+config.getEventjson());
                                        String resStr = service.execute(config.getEventkey(),config.getEventjson().replaceAll("%s",cellValue));
                                        System.out.println("esb查询结果:"+resStr);
                                        JSONObject result = JSON.parseObject(resStr);
                                        Object replaceVal = null;
                                        if (result.getInteger("code").equals(100)){
                                            JSONObject data = result.getJSONObject("data");
                                            replaceVal = searchNestedKey(data, config.getEventResKey());//获取回显字段的值
                                        }
                                        if (Objects.isNull(replaceVal)){
                                            item.put("_"+column,cellValue);
                                        }else{
                                            item.put("_"+column, replaceVal +"|"+cellValue);
                                        }
                                        ESB_MAP.put(config.getEventkey()+"_"+config.getEventjson().replaceAll("%s",cellValue),String.valueOf(item.get("_"+column)));
                                    }else{
                                        item.put("_"+column, val);
                                    }
                                }else if (!Objects.isNull(config.getSqlid())){
                                    String real_sql = SQL_MAP.get(config.getSqlid());
                                    if (Objects.isNull(real_sql)){
                                        RecordSet rs = new RecordSet();
                                        System.out.println("开始执行SQL");
                                        rs.executeQuery("select real_sql from OA_CUSTOM_SQL_CONFIG where sql_id=?",config.getSqlid());
                                        if(rs.next()){
                                            real_sql = rs.getString(1);
                                            SQL_MAP.put(config.getSqlid(),real_sql);
                                            String rep_sql = real_sql.replaceAll("%s",cellValue);
                                            if (!Objects.isNull(SQL_RES.get(rep_sql))){
                                                item.put("_" + column, SQL_RES.get(rep_sql));
                                                continue;
                                            }
                                            RecordSet rs1 = new RecordSet();
                                            rs1.executeQuery(rep_sql);
                                            if (rs1.next()){
                                                Object data = rs1.getArray().get(0);
                                                if (data.getClass().isArray()) {
                                                    Object[] dataArray = (Object[]) data;
                                                    if (dataArray.length > 0) {
                                                        Object element = dataArray[0];
                                                        if (element != null) {
                                                            String strValue = String.valueOf(element);
                                                            if (!strValue.equals(cellValue)) {
                                                                item.put("_" + column, strValue + "|" + cellValue);
                                                            } else {
                                                                item.put("_" + column, strValue);
                                                            }
                                                        } else {
                                                            item.put("_" + column, "");
                                                        }
                                                    } else {
                                                        item.put("_" + column, "");
                                                    }
                                                } else {
                                                    item.put("_" + column, data);
                                                }
                                                SQL_RES.put(rep_sql,String.valueOf(item.get("_" + column)));
                                            }
                                        }else{
                                            item.put("_"+column,cellValue);
                                        }
                                    }else{
                                        String rep_sql = real_sql.replaceAll("%s",cellValue);
                                        if (!Objects.isNull(SQL_RES.get(rep_sql))){
                                            item.put("_"+column, SQL_RES.get(rep_sql));
                                            continue;
                                        }
                                        RecordSet rs1 = new RecordSet();
                                        rs1.executeQuery(rep_sql);
                                        if (rs1.next()){
                                            Object data = rs1.getArray().get(0);
                                            if (data.getClass().isArray()) {
                                                Object[] dataArray = (Object[]) data;
                                                if (dataArray.length > 0) {
                                                    Object element = dataArray[0];
                                                    if (element != null) {
                                                        String strValue = String.valueOf(element);
                                                        if (!strValue.equals(cellValue)) {
                                                            item.put("_" + column, strValue + "|" + cellValue);
                                                        } else {
                                                            item.put("_" + column, strValue);
                                                        }
                                                    } else {
                                                        item.put("_" + column, "");
                                                    }
                                                } else {
                                                    item.put("_" + column, "");
                                                }
                                            } else {
                                                item.put("_" + column, data);
                                            }
                                            SQL_RES.put(rep_sql,String.valueOf(item.get("_"+column)));
                                        }
                                    }
                                }else{
                                    item.put("_"+column,cellValue);
                                }
                            }else if(type.equals("text")){
                                item.put(column,cellValue);
                            }else{
                                item.put(column,cellValue);
                            }
                        }else{//如果不在则直接赋值原值
                            //对日期格式进行重新格式化
                            String date = validateAndFormat(cellValue);
                            item.put(column,date);
                        }
                    }
                    jsonArray.add(item);
                }
                if (saveFile.exists()){
                    saveFile.delete();//用完后删除
                }
                return Result.ok(jsonArray);
            } catch (IOException e) {
                e.printStackTrace();
                return Result.error(e.getMessage());
            }
        }catch (Exception ex){
            ex.printStackTrace();
            new BaseBean().errorLog("解析失败!",ex);
            if (saveFile.exists()){
                saveFile.delete();//用完后删除
            }
            return Result.error(ex.getMessage());
        }
    }

    public static Object searchNestedKey(JSONObject obj, String targetKey) {
        for (Object key : obj.keySet()) {
            Object value = obj.get(key);
            if (value instanceof JSONObject) {
                return searchNestedKey((JSONObject) value, targetKey);
            } else if (value instanceof JSONArray) {
                return searchNestedArray((JSONArray) value, targetKey);
            } else if (key.equals(targetKey)) {
                return value;
            }
        }
        return null;
    }

    public static Object searchNestedArray(JSONArray arr, String targetKey) {
        for (int i=0;i<arr.size();i++) {
            Object value = arr.get(i);
            if (value instanceof JSONObject) {
                return searchNestedKey((JSONObject) value, targetKey);
            } else if (value instanceof JSONArray) {
                return searchNestedArray((JSONArray) value, targetKey);
            } else if (value.equals(targetKey)) {
                return value;
            }
        }
        return null;
    }
     // 创建一个全局 DataFormatter 实例
    private static String getCellValue(Cell cell) {
        if(Objects.isNull(cell)){
            return "";
        }
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    // 使用 DataFormatter 将数字单元格值（包括日期时间）转换为字符串
                    return dataFormatter.formatCellValue(cell);
                case BOOLEAN:
                    return Boolean.toString(cell.getBooleanCellValue());
                // ... 其他类型处理（如 FORMULA, BLANK, ERROR 等）
                default:
                    return "";
            }
    }

    public static class Config{
        String type;
        String kvMode;
        Integer index;
        Boolean multi;
        String sqlid;//sqlid   查询OA_CUSTOM_SQL_CONFIG
        String eventkey;
        String eventjson;
        String eventResKey;
        JSONObject dict;
        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public String getKvMode() {
            return kvMode;
        }

        public void setKvMode(String kvMode) {
            this.kvMode = kvMode;
        }

        public Integer getIndex() {
            return index;
        }

        public void setIndex(Integer index) {
            this.index = index;
        }

        public Boolean getMulti() {
            return multi;
        }

        public void setMulti(Boolean multi) {
            this.multi = multi;
        }

        public String getSqlid() {
            return sqlid;
        }

        public void setSqlid(String sqlid) {
            this.sqlid = sqlid;
        }

        public String getEventkey() {
            return eventkey;
        }

        public void setEventkey(String eventkey) {
            this.eventkey = eventkey;
        }

        public JSONObject getDict() {
            return dict;
        }

        public void setDict(JSONObject dict) {
            this.dict = dict;
        }

        public String getEventjson() {
            return eventjson;
        }

        public void setEventjson(String eventjson) {
            this.eventjson = eventjson;
        }

        public String getEventResKey() {
            return eventResKey;
        }

        public void setEventResKey(String eventResKey) {
            this.eventResKey = eventResKey;
        }
    }

}
